# -*- coding: utf-8 -*-
import sys
sys.path.append("..")

from MysqlCon import MysqlCon
#import pandas as pd
import pymysql
import time


class GenGeography2(object):
    """
    #insert into dw.dim_geography(country_id, country_name, lng, lat, grade) values (100000, '中华人民共和国', '116.368', '39.9151', 1);

    select * from block_dict where city_id not in (SELECT city_id from dim_city);
    update block_dict set city_id = 110000 where city_id = 110100;
    update block_dict set city_id = 120000 where city_id = 120100;
    update block_dict set city_id = 310000 where city_id = 310100;
    update block_dict set city_id = 500000 where city_id = 500100;

    select * from block_dict where county_id not in (SELECT region_id from dim_region);
    update block_dict set county_id = 330112 where county_id = 330185;
    update block_dict set county_id = 370115, county_name = '济阳区' where county_id = 370125;
    update block_dict set county_id = 510118, county_name = '新津区' where county_id = 510132;
    """


    #查询省份数据
    def query_province(self, connect, cursor):
        connect.ping(reconnect = True)
        query_sql = "SELECT province_id, province_name, lng, lat, boundary FROM dim_province"
        try:
            cursor.execute(query_sql)
            queryResult = cursor.fetchall()

        except Exception as e:
            print(e)
        #finally:
        #dataf = pd.DataFrame(queryResult)
        #dataf.columns = [ 'full_name', 'adcode', 'lng', 'lat', 'boundary']

        return queryResult

    #查询城市数据
    def query_city(self, connect, cursor, province_id):
        connect.ping(reconnect = True)
        query_sql = "SELECT city_id, full_name, big_region, first_letter, city_group, city_level, city_level_desc, lng, lat, boundary, city_name FROM dim_city where province_id = %s"
        try:
            cursor.execute(query_sql, (province_id))
            queryResult = cursor.fetchall()

        except Exception as e:
            print(e)
        #finally:

        return queryResult


    #查询区县数据
    def query_region(self, connect, cursor, city_id):
        connect.ping(reconnect = True)
        query_sql = "SELECT region_id, region_name, lng, lat, boundary, region_short FROM dim_region where city_id = %s"
        try:
            cursor.execute(query_sql, (city_id))
            queryResult = cursor.fetchall()

        except Exception as e:
            print(e)
        #finally:

        return queryResult


    #查询板块数据，板块可能是跨区的
    def query_block(self, connect, cursor, city_id):
        connect.ping(reconnect = True)
        query_sql = "SELECT county_id, block_name, lng, lat, boundary FROM block_dict where city_id = %s"
        try:
            cursor.execute(query_sql, (city_id))
            #cursor.execute(query_sql, (city_id, region_id))
            queryResult = cursor.fetchall()

        except Exception as e:
            print(e)
        #finally:

        return queryResult

    #多行插入
    def insert_many(self, connect, cursor, insert_sql, insert_data):
        connect.ping(reconnect = True)
        # 批量插入
        try:
            res = cursor.executemany(insert_sql, insert_data)
            print("res = ", res)
            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return True

    #单行插入返回ID
    def insert_one(self, connect, cursor, insert_sql, insert_data):
        id = 0
        # 批量插入
        try:
            connect.ping(reconnect = True)
            cursor.execute(
                insert_sql, insert_data
            )
            # 最后插入行的主键id，使用 cursor.lastrowid 和 connect.insert_id() 时一定要在 conn.commit() 之前
            id = cursor.lastrowid
            #print(connect.insert_id())

            # 提交，不进行提交无法保存到数据库
            connect.commit()
        except Exception as e:
            print(e)
            connect.rollback()
        #finally:
        #connect.close()

        return id

if __name__ == "__main__":
    # 获取数据库连接
    #mysql
    mysqlConn = pymysql.connect(host='172.28.56.90', user='mysql', password='egSQ7HhxajHZjvdX', port=3306, database='dw',charset="utf8")
    cursor = mysqlConn.cursor()

    #记录执行开始时间
    str_time = int(time.time())

    #实例化类
    obj = GenGeography2()

    insert_sql = """
        insert into dim_geography2(
            country_id, country_name, province_id, province_name, city_id, city_name, city_short, city_first_letter, city_zone, city_group, city_level, city_level_desc, region_id, region_name, region_short, block_name, grade, lng, lat, boundary, parent_id
            ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
        """
    #insert into dw.dim_geography(country_id, country_name, lng, lat, grade) values (100000, '中华人民共和国', , , 1);
    geoList = []
    country_id = 100000
    country_name = '中华人民共和国'
    province_id = None
    province_name = None
    city_id = None
    city_name = None
    city_short = None
    city_first_letter = None
    city_zone = None
    city_group = None
    city_level = None
    city_level_desc = None
    region_id = None
    region_name = None
    region_short = None
    block_name = None
    grade = 1
    lng = '116.368'
    lat = '39.9151'
    boundary = None
    parent_id = None
    geo = (country_id, country_name, province_id, province_name, city_id, city_name, city_short, city_first_letter, city_zone, city_group, city_level, city_level_desc, region_id, region_name, region_short, block_name, grade, lng, lat, boundary, parent_id)
    country_pid = obj.insert_one(mysqlConn, cursor, insert_sql, geo)
    #exit()

    provinceData = obj.query_province(mysqlConn, cursor)
    print("province total:", len(provinceData))

    for province in provinceData:
        #query_sql = "SELECT province_id, province_name, lng, lat, boundary FROM dim_province"
        province_id = province[0]
        province_name = province[1]
        city_id = None
        city_name = None
        city_short = None
        city_first_letter = None
        city_zone = None
        city_group = None
        city_level = None
        city_level_desc = None
        region_id = None
        region_name = None
        region_short = None
        block_name = None
        grade = 2
        lng = province[2]
        lat = province[3]
        boundary = province[4]
        parent_id = country_pid
        geo = (country_id, country_name, province_id, province_name, city_id, city_name, city_short, city_first_letter, city_zone, city_group, city_level, city_level_desc, region_id, region_name, region_short, block_name, grade, lng, lat, boundary, parent_id)
        province_pid = obj.insert_one(mysqlConn, cursor, insert_sql, geo)

        print(province_id, ' ', province_name, '; parent_id:', country_pid)

        cityData = obj.query_city(mysqlConn, cursor, province_id)
        for city in cityData:
            #query_sql = "SELECT city_id, full_name, big_region, first_letter, city_group, city_level, city_level_desc, lng, lat, boundary, city_name FROM dim_city where province_id = %s"
            city_id = city[0]
            city_name = city[1]
            city_short = city[10]
            city_first_letter = city[3]
            city_zone = city[2]
            city_group = city[4]
            city_level = city[5]
            city_level_desc = city[6]
            region_id = None
            region_name = None
            region_short = None
            block_name = None
            grade = 3
            lng = city[7]
            lat = city[8]
            boundary = city[9]
            parent_id = province_pid
            geo = (country_id, country_name, province_id, province_name, city_id, city_name, city_short, city_first_letter, city_zone, city_group, city_level, city_level_desc, region_id, region_name, region_short, block_name, grade, lng, lat, boundary, parent_id)
            city_pid = obj.insert_one(mysqlConn, cursor, insert_sql, geo)

            print(city_id, ' ', city_name, '; parent_id:', province_pid)

            regionData = obj.query_region(mysqlConn, cursor, city_id)
            blockData = obj.query_block(mysqlConn, cursor, city_id)
            for region in regionData:
                #query_sql = "SELECT region_id, region_name, lng, lat, boundary, region_short FROM dim_region where city_id = %s"
                region_id = region[0]
                region_name = region[1]
                region_short = region[5]
                block_name = None
                grade = 4
                lng = region[2]
                lat = region[3]
                boundary = region[4]
                parent_id = city_pid
                geo = (country_id, country_name, province_id, province_name, city_id, city_name, city_short, city_first_letter, city_zone, city_group, city_level, city_level_desc, region_id, region_name, region_short, block_name, grade, lng, lat, boundary, parent_id)
                region_pid = obj.insert_one(mysqlConn, cursor, insert_sql, geo)

                print(region_id, ' ', region_name, '; parent_id:', city_pid)

                for block in blockData:
                    #query_sql = "SELECT county_id, block_name, lng, lat, boundary FROM block_dict where city_id = %s"
                    county_id = block[0]
                    if county_id == None :
                        continue
                    block_name = block[1]
                    grade = 5
                    lng = block[2]
                    lat = block[3]
                    boundary = block[4]
                    parent_id = region_pid
                    #print(county_id, ' ', block_name)
                    if county_id == region_id :
                        geo = (country_id, country_name, province_id, province_name, city_id, city_name, city_short, city_first_letter, city_zone, city_group, city_level, city_level_desc, region_id, region_name, region_short, block_name, grade, lng, lat, boundary, parent_id)
                        pid = obj.insert_one(mysqlConn, cursor, insert_sql, geo)
                        if pid > 0 :
                            blockData.remove(block)
            #添加区域id为空的记录
            for block in blockData:
                #query_sql = "SELECT county_id, block_name, lng, lat, boundary FROM block_dict where city_id = %s"
                county_id = block[0]
                block_name = block[1]
                grade = 5
                lng = block[2]
                lat = block[3]
                boundary = block[4]
                parent_id = city_pid
                geo = (country_id, country_name, province_id, province_name, city_id, city_name, city_short, city_first_letter, city_zone, city_group, city_level, city_level_desc, region_id, region_name, region_short, block_name, grade, lng, lat, boundary, parent_id)
                geoList.append(geo)
            if len(geoList) > 0 :
                print("geoList:", len(geoList), '; parent_id:', city_pid)
                obj.insert_many(mysqlConn, cursor, insert_sql, geoList)
                geoList.clear()
        print("#################################")

    mysqlConn.close()

    end_time = int(time.time())
    print("程序执行时间（秒）：", end_time - str_time)
